import psycopg2
import requests

def get_pg_tables():
    conn = psycopg2.connect(
        host="127.0.0.1",
        database="benxi",
        user="postgres",
        password="egova"
    )
    query_sql = """
            SELECT 
                f_table_name as table_name,
                type as geometry_type
            FROM geometry_columns
            WHERE f_table_schema NOT IN ('information_schema', 'pg_catalog')
        """
    names = []
    try:
        # 使用可滚动游标
        with conn.cursor() as cursor:
            cursor.execute(query_sql)
            while True:
                rows = cursor.fetchmany(100)
                if not rows:
                    break

                for row in rows:
                    table_name, type = row
                    names.append(table_name)

    except Exception as e:
        conn.rollback()
        print(f"更新失败: {e}")
    finally:
        conn.close()
    return names


def register(url, table_name, store_name, workspace_name, minx, maxx, miny, maxy, epsg):
    payload = f"""
    <featureType>
        <name>{table_name}</name>
        <title>{table_name}</title>
        <srs>EPSG: {epsg}</srs>
        <nativeBoundingBox>
            <minx>{minx}</minx>
            <maxx>{maxx}</maxx>
            <miny>{miny}</miny>
            <maxy>{maxy}</maxy>
            <crs>EPSG: {epsg}</crs>
        </nativeBoundingBox>
    </featureType>
    """
    headers = {
        'Content-Type': 'application/xml'
    }
    url = url + "/rest/workspaces/" + workspace_name + "/datastores/" + store_name + "/featuretypes.json"
    response = requests.request("POST", url, headers=headers, data=payload)
    if (response.status_code == 201):
        print(f"{table_name}注册成功")
    else:
        print(f"{table_name}注册失败")




if __name__ == '__main__':
    url = "http://127.0.0.1:8080/geoserver/"
    store_name = "benxi"
    workspace_name = "urbanlayer1"
    minx, maxx, miny, maxy = 121.770519, 125.770519,39.297909,43.297909
    epsg = "4490"
    for name in get_pg_tables():
        register(url, name, store_name, workspace_name, minx, maxx, miny, maxy, epsg)
